package com.ffms.dao.impl;

import com.ffms.dao.FamilyMemberDao;
import com.ffms.dao.ShouruDao;
import com.ffms.entity.Admin;
import com.ffms.entity.FamilyMember;
import com.ffms.entity.Shouru;
import com.ffms.util.JDBCUtils;

import java.lang.reflect.Member;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 从卿
 * @create 2022/3/4-20:12
 */
public class FamilyMemberDaoImpl implements FamilyMemberDao {


    @Override
//    查询所有家庭成员
    public List<FamilyMember> findAll() {
        List<FamilyMember> familyMemberList = new ArrayList<FamilyMember>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String sid = rs.getString("sid");
                String name = rs.getString("name");
                String pwd = rs.getString("pwd");
//封装
                FamilyMember member = new FamilyMember(Integer.parseInt(sid), name, pwd);
//                将对象放入集合
                familyMemberList.add(member);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return familyMemberList;
    }

    @Override
    //增加新的家庭成员
    public int save(FamilyMember fm) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "insert into tables.t_member values (sid,?,?)";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {fm.getName(), fm.getPwd()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;

    }

    //修改用户信息
    @Override
    public int update(FamilyMember fm) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "update tables.t_member set pwd=? where name=? ";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {fm.getPwd(), fm.getName()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }


    //删除成员信息
    @Override
    public void delete(String name) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "delete from tables.t_member where name=?";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {name};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    @Override
    public FamilyMember findByName(String name) {
        FamilyMember fm = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member where name = '" + name + "' ";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {

                String pwd = rs.getString("pwd");
//封装至对象中
                fm = new FamilyMember(name, pwd);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return fm;

    }

    //家庭成员的登录
    @Override
    public FamilyMember findfm(String name, String pwd) {
        FamilyMember fm = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member where name = '" + name + "' and pwd = '" + pwd + "' ";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {

                fm = new FamilyMember(Integer.parseInt(rs.getString("sid")), name, pwd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return fm;
    }

    //判断成员姓名是否存在
    @Override
    public FamilyMember findfmByName(String userid) {
        FamilyMember fm = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member where name = '" + userid + "' ";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {


                fm = new FamilyMember(userid);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return fm;
    }

    //判断成员密码是否正确
    @Override
    public FamilyMember findfmByPwd(String password) {
        FamilyMember fm = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member where  pwd = '" + password + "' ";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {


                fm = new FamilyMember(password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return fm;
    }

    //单个成员的收入情况
    @Override
    public FamilyMember findAllshouru(int sid, String name, String pwd) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        FamilyMember familyMember = null;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                //获取各个字段的值

                familyMember = new FamilyMember(sid, name, pwd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return familyMember;

    }

    //管理员查看所有家庭成员的收入情况
    @Override
    public List<FamilyMember> findAllshouru() {
        List<FamilyMember> familyMemberList = new ArrayList<FamilyMember>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int sid = rs.getInt("sid");
                String name = rs.getString("name");
                String pwd = rs.getString("pwd");

//封装
                FamilyMember member = new FamilyMember(sid, name, pwd);
//                将对象放入集合
                familyMemberList.add(member);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return familyMemberList;
    }
    @Override
    public FamilyMember findAllByName(String name) {
        FamilyMember fm = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                int sid=rs.getInt("sid");
                String pwd = rs.getString("pwd");
//封装至对象中
                fm = new FamilyMember(sid,name, pwd);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return fm;

    }

}
